/**
 * 
 */
package mysql;
import java.awt.print.Printable;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.WriteAbortedException;
/**
*@author tonychao 
*功能
*/
//STEP 1. Import required packages
import java.sql.*;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Map;
import java.util.zip.Inflater;

import org.json.JSONException;

import confs.DataType;
import confs.SingleParameter;
import others.Global;
import fetcher.APIFetcher;
import fetcher.RunRecord;
import graybox.CallPythonWindows;
import graybox.ParameterSearch;

//import org.apache.hadoop.io.retry.RetryPolicy.RetryAction;
//import org.apache.zookeeper.proto.WatcherEvent;

//import com.sun.xml.bind.v2.runtime.reflect.opt.FieldAccessor_Float;

import shellInterface.UserSubmitInterface_test;
//import tuningModel.ParameterSearch;

public class DB {
	/**
	 * @param args
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	private  Connection connection;
	//建立和数据库的连接
	public  void initConnection() throws ClassNotFoundException, SQLException{
		//STEP 2: Register JDBC driver
		// 驱动程序JDBC提供了统一的接口，但是对每个数据库的对接有需要另一套jar包
		//String driver = "com.mysql.jdbc.Driver";
		//Class.forName(driver);
		
		//STEP 3: Open a connection
		// URL，用户，server—URL-database
		//虚拟机上的数据库-NAT
		final String URL = Global.CURRENT_DB+"?connectTimeout=5000";
		final String USER = Global.USER_NAME;
		final String PASSWORD = Global.PASS_WORD;
		DBOutPut(URL);
		DBOutPut("Connecting to database...");
		connection = DriverManager.getConnection(URL, USER, PASSWORD);
		if (!connection.isClosed()) DBOutPut("Succeeded connecting to the Database!");
	}
	
	/**
	 * @param runRecord
	 * @return
	 * @throws SQLException
	 * @throws InterruptedException
	 * @throws IOException
	 */
	
	//将一个record写进DB
	public Boolean writeRecordIntoDB(RunRecord runRecord) throws SQLException, InterruptedException, IOException{
		
		//将参数压缩成字符串
		String confListInInt1, confListInInt2, confListInInt3;
		confListInInt1=confListInInt2= confListInInt3="";
		Map<String, SingleParameter> parameterMap =runRecord.conf.parameterMap;
		
		//搜索每一组的参数并将其转换为int型，注意，其顺序由GlobalStatics 中的次序给次给定，且必须被严格遵守
		SingleParameter tmpParameter=null;
		for (String tmp: Global.CHOSEN_PARAMETERS_GROUP1){
			//if (tmp.equals("dummy")) continue;
			if(parameterMap.containsKey(tmp)){
				tmpParameter=parameterMap.get(tmp);
				confListInInt1 += String.valueOf(tmpParameter.value)+"\t";
			}
		}
		
		for (String tmp: Global.CHOSEN_PARAMETERS_GROUP2){
			if (tmp.equals("dummy")){
				confListInInt2 += String.valueOf(-1)+"\t";
				continue;
			}
			if(parameterMap.containsKey(tmp)){
				tmpParameter=parameterMap.get(tmp);
				confListInInt2 += String.valueOf(tmpParameter)+"\t";
			}
		}
		
		for (String tmp: Global.CHOSEN_PARAMETERS_GROUP3){
			if (tmp.equals("dummy")){
				confListInInt3 += String.valueOf(-1)+"\t";
				continue;
			}
			if(parameterMap.containsKey(tmp)){
				tmpParameter=parameterMap.get(tmp);
				confListInInt3 += String.valueOf(tmpParameter)+"\t";
			}
		}
		
		long group1_time_ms=-1;
		long group2_time_ms=-1;
		long group3_time_ms=-1;
		
		//目前暂时先使用总时间
		group1_time_ms =runRecord.getApplicationEndTime()-runRecord.getApplicationStartTime();
		

		//向里面写数据，但是原则上只写 存在app_id 的数据
		//首先查询App_id 是否已经存在
		Statement statement = connection.createStatement();
		String sql="";
			if (has_app_id(runRecord.getApplicationID())){//如果存在app_id则插入其信息
				//System.err.println("sadfsadfasdfsadfsadfasdfsadf");
				DBOutPut("该app_id 已经存在于run_record表中");
				sql= "UPDATE sparkTuningDB.run_record SET app_name=\' "
						+String.valueOf(runRecord.getApplicationName())+"\',"
						+"total_run_time_ms="+"\'"+String.valueOf(runRecord.getApplicationEndTime()-runRecord.getApplicationStartTime())+"\',"
						+"group1_conf=" +"\'"
						+confListInInt1+"\',"
						+"group2_conf=" +"\'"
						+confListInInt2+"\',"
						+"group3_conf=" +"\'"
						+confListInInt3+"\',"
						+"group1_time_ms=" +"\'"
						+group1_time_ms+"\',"
						+"group2_time_ms=" +"\'"
						+group2_time_ms+"\',"
						+"group3_time_ms=" +"\'"
						+group3_time_ms+"\'"
						+"WHERE app_id=\'"
						+runRecord.getApplicationID()+"\';";
						DBOutPut(sql);
						statement.executeUpdate(sql);
						
						sql="select * from run_record where app_id = \'"+runRecord.getApplicationID()+"\'";
				
						DBOutPut(sql);
						ResultSet result =statement.executeQuery(sql);
						while(result.next()){
							System.out.println(result.getString("group1_conf"));
						}
			}
			
			
			else{//如果不存在，则直接写入
			sql="INSERT INTO run_record (app_id,app_name,total_run_time_ms,group1_conf,group2_conf,group3_conf) VALUES (\'"+
					String.valueOf(runRecord.getApplicationID())+ "\' , \'"+
					String.valueOf(runRecord.getApplicationName())+"\' , \'" +
					String.valueOf(runRecord.getApplicationEndTime()-runRecord.getApplicationStartTime())+"\' ,"+
					"\'"+confListInInt1+"\',"+
					"\'"+confListInInt2+"\',"+
					"\'"+confListInInt3+"\',"+
					"\'"+group1_time_ms+"\',"+
					"\'"+group2_time_ms+"\',"+
					"\'"+group3_time_ms+"\'"+
					");";
			DBOutPut(sql);
			statement.executeUpdate(sql);
			
			}
		statement.close();
		//求这个id的MD5
		String MD5= getMD5ByID(runRecord.getApplicationID());
		String className=getClassNameByID(runRecord.getApplicationID());
		//增加application表  succeed_count 的计数
		add_succeed_count(MD5,className);
		//检查之，如果计数 达到一定数量则调用训练脚本
		return false;
	}
	
	
	
	
	/**
	 * @param applicationID
	 * @return
	 * @throws SQLException 
	 */
	private String getClassNameByID(String applicationID) throws SQLException {
		/**
		*创建时间：2017年7月29日
		*输入
		*输出
		*/
		Statement statement = connection.createStatement();
		String sql="select app_class from run_record where app_id =\'"+
				applicationID+"\';";
		DBOutPut(sql);
		ResultSet result = statement.executeQuery(sql);
		result.first();
		//System.err.println();
		return result.getString("app_class");

	}

	/**
	 * 表 run_record 由id查询MD5
	 * @param applicationID
	 * @return
	 * @throws SQLException 
	 */
	private String getMD5ByID(String applicationID) throws SQLException {
		Statement statement = connection.createStatement();
		String sql="select app_md5 from run_record where app_id =\'"+
				applicationID+"\';";
		DBOutPut(sql);
		ResultSet result = statement.executeQuery(sql);
		result.first();
		//System.err.println();
		return result.getString("app_md5");
	}


	
	
	/**
	 * 表 run_record 
	 * 按照MD5值增加记录数量计数
	 * @throws SQLException 
	 * @throws InterruptedException 
	 * @throws IOException 
	 */
	public void add_succeed_count(String MD5,String className) throws SQLException, InterruptedException, IOException{
		Statement statement = connection.createStatement();
		String sql = "select succeed_record_count,last_model_trained from application where app_md5=\'"+
		MD5 +"\' and app_class=\'"+className+"\';";
		DBOutPut(sql);
		ResultSet result = statement.executeQuery(sql);
		result.first();
		//System.err.println();
		long currentCount=result.getLong("succeed_record_count");
		long last_model_trained = result.getLong("last_model_trained");
		//System.err.println(currentCount);
		currentCount ++;
		//System.err.println(currentCount);
		sql= "UPDATE application SET succeed_record_count=\' "
				 +currentCount+"\'"
				 +"WHERE app_md5= \'"
				 +MD5
				 + "\' and app_class=\'"+className+"\';";
		DBOutPut(sql);
		statement.executeUpdate(sql);
		
		
		long record_unused=currentCount-last_model_trained;
		if (record_unused>=Global.RETRAIN_COUNT&&record_unused%Global.MIN_FACTOR==0){
			System.err.println("#############################################################################################");
			UserSubmitInterface_test.UIOutPut("未使用的模型数量已经达标，重新训练");
			int ret =callTrainModel(MD5,className);
			if (ret==0){//建模成功
				UserSubmitInterface_test.UIOutPut("重置record_unused:last_model_trained");
			sql= "UPDATE application SET last_model_trained=\' "
					 +currentCount+"\',"
					 +"has_model=\'1\'"
					 +"WHERE app_md5= \'"
					 +MD5
					 + "\' and app_class=\'"+className+"\';";
			DBOutPut(sql);
			statement.executeUpdate(sql);
			}
			else {//建模失败
				System.err.println("模型建立失败！");
			}			
			
			System.err.println("#############################################################################################");
		}
		statement.close();
		return ;
	}
	


	/**
	 * 调用模块训练模型
	 * 此函数调用Python脚本重新训练模型，并且直接覆盖原有模型
	 * @throws InterruptedException 
	 * @throws IOException 
	 */
	public int callTrainModel(String MD5,String className) throws  IOException, InterruptedException{
		//开出去一个线程，然后就啥也不管了 也是美滋滋 然而我们的输出,以及waitfor也使得这个函数是阻塞式的
		CallPythonWindows tmp = new CallPythonWindows();
		int ret=tmp.pythonRun(Global.PYTHON_MAKE_MODEL_SCRIPT_DB,new String[]{MD5,className});
		return ret;
	}
	
	
	
	
	
	
	
	/**
	 * @param applicationID
	 * @return
	 * 此函数用于查询 run_record 表中是否以经有App——id
	 * @throws SQLException 
	 */
	private boolean has_app_id(String applicationID) throws SQLException {
		Statement statement = connection.createStatement();
		String sql = "select * from run_record where app_id = \'"+applicationID+"\';";
		DBOutPut(sql);
		ResultSet result = statement.executeQuery(sql);
		//如果结果为空
		if(!result.first()){
			return false;
		}
		return true;
	}

	public static void  DBOutPut(String line) {
		System.out.println("[数据库]:\t"+line);
	}
	
	
	/**
	 * 
	 * @param programMD5
	 * @return
	 * @throws SQLException
	 */
	public  boolean  isRegisted(String programMD5,String className) throws SQLException{
		//STEP 4: Execute a query
		Statement statement = connection.createStatement();
		String sql = "select * from application where app_md5 = \'"+programMD5+"\' AND app_class=\'"+className+"\' ;";
		UserSubmitInterface_test.UIOutPut(sql);
		ResultSet result = statement.executeQuery(sql);
		//如果结果为空
		if(!result.first()){
			return false;
		}
		while(result.next()){
			System.out.println(result.getString(programMD5));
		}
		result.close();
		statement.close();
		return true;
	}
	
	public  boolean  isRegisted(String programMD5) throws SQLException{
		return isRegisted(programMD5,"empty_class_name");
	}
	

	
	/**
	 * 
	 * @param programMD5
	 * @throws SQLException
	 */
	public  void registe(String programMD5,String className) throws SQLException{
		Statement statement = connection.createStatement();
		String sql="INSERT INTO application (app_md5,app_class) VALUES (\'"+programMD5+"\' , \'"+className+"\'  )";
		UserSubmitInterface_test.UIOutPut(sql);
		statement.executeUpdate(sql);
		statement.close();
	}
	
	public  void registe(String programMD5) throws SQLException{
		 registe(programMD5,"empty_class_name");
		 return;
	}
	
	public  void close() throws SQLException{
		if (connection!=null&&!connection.isClosed()) connection.close();
	}
	
	/**
	 * 
	 * @param programMD5
	 * @return
	 * @throws SQLException
	 */
	public  boolean hasModel(String programMD5,String className) throws SQLException{
		//STEP 4: Execute a query
		Statement statement = connection.createStatement();
		String sql = "select has_model from application where app_md5 = \'"+programMD5+"\' AND  app_class =\'"+className+"\';";
		UserSubmitInterface_test.UIOutPut(sql);
		ResultSet result = statement.executeQuery(sql);
		//如果结果为空
		boolean flag=false;
		if(!result.first()){
			return false;
		}
		do{
			flag= result.getBoolean("has_model");
		}while(result.next());
		result.close();
		statement.close();
		return flag;
	}
	
	public boolean hasModel(String programMD5) throws SQLException{
		return hasModel(programMD5,"empty_class_name");
	}



	/**
	 *	用于创建一个新的运行数据记录 ,将MD5和数据的app_jd绑定
	 *执行于调用优化端口进行调优时
	 * @param programMD5
	 * @param className
	 * @param app_id
	 * @return
	 * @throws SQLException 
	 * @throws InterruptedException 
	 * @throws IOException 
	 * @throws ParseException 
	 * @throws JSONException 
	 */
	public String addRunRecord(String programMD5, String className, String app_id,int sleepTimeInMs) throws SQLException, InterruptedException, JSONException, ParseException, IOException {

		Statement statement = connection.createStatement();
		String sql="INSERT INTO run_record (app_md5,app_class,app_id,total_run_time_ms) VALUES (\'"+programMD5+"\' , \'"+className+"\' ,\'"+app_id+"\',\'"+"-1"+" \')";
		UserSubmitInterface_test.UIOutPut(sql);
		statement.executeUpdate(sql);
		statement.close();
		// TODO Auto-generated method stub
		
		Thread.sleep(sleepTimeInMs);
		RunRecord record =new APIFetcher().readRunRecord(app_id);
		writeRecordIntoDB(record);
		DBOutPut("添加新的运行记录");
		return null;
	}

	
	/**
	 * 
	 * 删除一个对一个ID的run_record  
	 * @param id
	 * @throws SQLException 
	 */
	public void deleteRunRecord(String app_id) throws SQLException {
		Statement statement= connection.createStatement();
		String sql="DELETE  From run_record where app_id =\'"+app_id+"\';";
		UserSubmitInterface_test.UIOutPut(sql);	
		statement.executeUpdate(sql);
		statement.close();
	}

	/**
	 * @param programMD5
	 * @param className
	 * @return
	 * 因为我们的优化过程走的是启发式+机器学习模式，所以我们需要从数据库的历史数据而非训练模型中获取优化效果的估计值
	 * @throws SQLException 
	 */
	@SuppressWarnings("unused")
	public double getaccRatio(String programMD5, String className) throws SQLException {
		/**
		*创建时间：2018年8月24日
		*输入
		*输出
		*/
		// TODO Auto-generated method stub
		if(Global.USE_DB_Flag==false||this.connection==null||this.connection.isClosed()){
			return 0.25+Math.random()/4; //无法连接历史数据库，但是通常来讲，理论上性能提升下界为40%左右
			
		}
		//以下是可以连接历史数据库的情况
		Statement statement= connection.createStatement();
		String sql = "select * from run_record where app_md5 = \'"+programMD5+"\' AND app_class=\'"+className+"\' ;";
		UserSubmitInterface_test.UIOutPut(sql);	
		ResultSet result = statement.executeQuery(sql);
		//如果结果为空，说明优化效果估计未知
		double ret =0;
		ArrayList<Long> timeList= new ArrayList<>();
		if(!result.first()){
			return -1;
		}
		while(result.next()){
			
			long tmp=result.getLong("total_run_time_ms");
			if(tmp!=-1){
				System.out.println(tmp);
				timeList.add(tmp);
			}
			
		}
		
		long min=Long.MAX_VALUE;
		long sum =0;
		long max=-1;
		for (Long k :timeList){
			sum+=k;
			if (k<min) min=k;
			if (k>max) max=k;
		}
		
		ret= min/(double)(sum/timeList.size());
		ret=1-ret;
		
		result.close();
		statement.close();
		return ret;
		
	}


	
	/*
	 * 
	 * legacy code
	public static void main(String[] args) throws SQLException, ClassNotFoundException {
		initConnection();
		
		registe("0987","word count");
		System.out.println(isRegisted("0987","wordcount"));
		System.out.println(hasModel("0987","word count"));
		
		
		
		//STEP 4: Execute a query
		Statement statement = connection.createStatement();
		String sql = "select * from application";
		ResultSet result = statement.executeQuery(sql);
		
		*//**
		 * sql = "DELETE FROM Employees";
		 * ResultSet rs = stmt.executeUpdate(sql);
		 * 
		 * sql = "SELECT id, first, last, age FROM Employees";
		 * ResultSet rs = stmt.executeQuery(sql);
		 * 
		 * *//*

		//关闭资源
		result.close();
		statement.close();
		connection.close();
		
		
	
		

	}
*/
}
